﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using ExcelApplication = Microsoft.Office.Interop.Excel.ApplicationClass; 


namespace ConsoleApplication1
{
    //操作Office的各种函数打包
     


    public static class OfficeWrapper
    {
        


        //以oledb的形式读取Excel
        //速度比较快
        public static System.Data.DataTable ReadExcelByOledb(string fileName,string sheetName)
        {
            string strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = "SELECT * FROM "+sheetName;
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            System.Data. DataTable dt = new System.Data.DataTable();
            myCommand.Fill(dt);
           
            myConn.Close();
            return dt;
        }


        //插入数据
        public static bool WriteExcelByOledb(string fileName,string sheetName,System.Data.DataTable dt_New)
        {
            string strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
            OleDbConnection myConn = new OleDbConnection(strCon);

            string strCom = "SELECT * FROM " + sheetName;
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            try
            {
                myCommand.Update(dt_New);
            }
            catch (Exception)
            {
                return false;
            }
            myConn.Close();
            return true;
        }

        /// <summary>
        /// 通过调用Excel的Com组件来向Excel中写入数据
        /// 一般针对模板，需要另外保存。
        /// 具体的规则可以复写
        /// 适用于少量的数据增加操作，同时还能设置样式。
        /// </summary>
        /// <param name="templatePath"></param>
        /// <param name="dt">这里传递数组</param>
        /// <param name="destPath"></param>
        /// <param name="i">第一个写入数据的行号,这个行号在Excel里面是以1开始的</param>
        /// <param name="j">第一个写入数据的列号</param>
        public static void WriteExcelByExcelApp(string templatePath, object[,] objArray,string destPath,int i,int j)
        {


            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            xlApp.DisplayAlerts = false; //合并单元格操作时候会出现警告，添加这一句可以将警告去除
            if (xlApp == null)
            {
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Open(templatePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                  
          
            //关于样式和边框的设置等看情况补充，这里默认增加数据区域的边框
           
            ////增加边框
            Range range = worksheet.get_Range(worksheet.Cells[i, j], worksheet.Cells[i+objArray.GetLength(0)-1 , j+objArray.GetLength(1)-1]);
            //使用数组批量，避免了在循环中重复使用Com对象，进一步增加大数据写入的销量
            range.set_Value(Missing.Value,objArray);
           
            range.Borders.LineStyle = 1;
 
            //保存工作空间         
            workbook.SaveAs(destPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //关闭工作空间
            workbook.Close(true, Type.Missing, Type.Missing);
            workbook = null;
            xlApp.Quit();
            xlApp = null;
        }


        //以变量替换的形式修改模板生成报表
        public static void WriteWordByWordApp()
        {

        }

        
        




    }
}
